package com.zygh.lz.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zygh.lz.entity.signfile.SignStatistics;

import com.zygh.lz.vo.ExcelDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface SignStatisticsMapper extends BaseMapper<SignStatistics> {


    int insertIgnoreNull(SignStatistics signStatistics);

    @Select("<script> " +
            "SELECT * FROM sign_statistics " +
            "<where> " +
            "sign_time BETWEEN #{startTime} AND  #{endTime}" +
            "<if test = 'sectionPid != null'>" +
            " AND (section_pid= #{sectionPid} or section_id = #{sectionPid}) " +
            "</if>" +
            "   ORDER BY section_id, mileage DESC " +
            "</where>" +
            "</script>")
    List<SignStatistics> findSignStatis(@Param("sectionPid") Integer sectionPid,
                                        @Param("startTime") String startTime,
                                        @Param("endTime") String endTime);

    @Select("<script> SELECT section_name,`name`,warning_signal,ROUND(SUM(mileage),2) mileage,SUM(illegal_num) illegal_num,SUM(problem_num) problem_num,\n" +
            "SUM(jiechu_num) jiechu_num,GROUP_CONCAT(sign_name separator',') sign_name,section_id," +
            "(SELECT COUNT(DISTINCT DATE_FORMAT(signintime,'%Y-%m-%d')) count FROM sign sg WHERE sg.warningsignal = s.warning_signal AND sg.signintime BETWEEN #{startTime} AND  #{endTime} ) sign_num,\n" +
            "CONCAT(SUM(substring_index(duration,'小',1))+(SUM(substring_index(substring_index(duration,'时',-1),'分',1)) DIV 60),'小时',(SUM(substring_index(substring_index(duration,'时',-1),'分',1)) MOD 60),'分钟') duration\n" +
            "FROM sign_statistics s " +
            "<where>  " +
            "sign_time BETWEEN #{startTime} AND  #{endTime}" +
            "<if test = 'sectionPid != null'>" +
            " AND (section_pid= #{sectionPid} or section_id = #{sectionPid}) " +
            "</if>" +
            "   GROUP BY warning_signal  ORDER BY section_id, ROUND(SUM(mileage),2) DESC " +
            "</where>" +
            "</script>")
    List<SignStatistics> findSignStatisByWeek(@Param("sectionPid") Integer sectionPid,
                                              @Param("startTime") String startTime,
                                              @Param("endTime") String endTime);


    @Select("SELECT IFNULL(SUM(SUBSTRING_INDEX(duration,'小',1))*60,0) `hours`," +
            "IFNULL(SUM(SUBSTRING_INDEX(SUBSTRING_INDEX(duration,'分',1),'时',-1)),0) `minutes`," +
            "IFNULL(ROUND(SUM(mileage),1),0)  totalDistance,MAX(sign_name) jobs\n" +
            "FROM sign_statistics WHERE staff_id = #{id} AND sign_time  BETWEEN #{startTime} AND #{endTime}")
    Map<String, Object> fingSignStationByDuty(@Param("id") Integer id,
                                              @Param("startTime") String startTime,
                                              @Param("endTime") String endTime);

    @Select("  select sum(mileage) as mileage," +
            " ss.section_id,ss.section_name,ss.name,ss.staff_id,ss.warning_signal,ss.duration,ss.section_pid,ss.illegal_num,ss.problem_num,ss.jiechu_num,ss.sign_name,ss.sign_num,ss.sign_time,ss.creat_time" +
            ", sf.staff_photo as staffPhoto from sign_statistics ss left join staff sf on ss.staff_id = sf.sys_staff_id " +
            " where ss.sign_time BETWEEN  #{startTime} and #{endTime}" +
            " and mileage != 0 " +
            "  group by staff_id " +
            "  order by mileage desc")
    List<SignStatistics> mileages(ExcelDto excelDto);

    @Select("  select sum(illegal_num) as illegalNum," +
            "     ss.section_id,ss.section_name,ss.name,ss.staff_id,ss.warning_signal,ss.duration,ss.mileage,ss.section_pid,ss.problem_num,ss.jiechu_num,ss.sign_name,ss.sign_num,ss.sign_time,ss.creat_time " +
            " ,sf.staff_photo as staffPhoto from sign_statistics ss left join staff sf on ss.staff_id = sf.sys_staff_id" +
            "  where " +
            " ss.sign_time BETWEEN  #{startTime} and #{endTime} " +
            " and illegal_num != 0 " +
            "  group by staff_id " +
            " order by illegal_num desc")
    List<SignStatistics> jiechuNum(ExcelDto excelDto);
}
